using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NeosSDI.Core;
using System.Data;
using Dapper;
using NeosSDI.Core.Manager;
using NeosSDI.Transverse.Interfaces.DAL;
using NeosSDI.Transverse.Models.ADO;
using System.Diagnostics;

namespace NeosSDI.DAL.ADO
{
    public class SampleADORepository : BaseRepository, ISampleADORepository
    {
        private BaseRepository baseRepository = new BaseRepository();


        /// <summary>
        /// Create the database
        /// </summary>
        /// <param name="script">creation sql script</param>
        public void CreateDatabase(string script)
        {
            SqlConnection connect = null;
            try
            {
                string _connectionString = ConfigurationManager.ConnectionStrings[WebConfig.Configuration.ConnectionStrings.DefaultConnection.ToDescription()].ConnectionString;
                _connectionString = _connectionString.Replace("SampleDatabase", "Master");
                connect = new SqlConnection(_connectionString);
                connect.Open();
                var _array = script.Replace("GO", "|").Split('|');
                foreach (var scr in _array)
                {
                    if (!string.IsNullOrEmpty(scr))
                    {
                        connect.Execute(scr);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                connect.Close();
            }

        }

        /// <summary>
        /// Check if database exists
        /// </summary>
        public bool CheckDatabaseExist()
        {
            return baseRepository.CallQuery((cnn) =>
            {
                string query = string.Format(DatabaseQuery.GetQuery("CheckDatabaseExist"), cnn.Database);
                if (cnn.Query<int>(query).Single() > 0)
                    return true;
                return false;
            });
        }



        /// <summary>
        /// Get all sample
        /// </summary>
        /// <returns>List of sample</returns>
        public List<SampleADODTO> GetList(SearchCriteriaDTO criteria = null)
        {
            var parameters = new DynamicParameters();
            if (criteria != null)
            {
                parameters.Add("@Keyword", criteria.Keyword);
            }

            return baseRepository.CallStoredProcedure((cnn) =>
            {
                return cnn.Query<SampleADODTO>(DatabaseQuery.GetStoredProcedured("GetListOfSamples"), parameters, commandType: CommandType.StoredProcedure).ToList();
            });
        }

        /// <summary>
        /// Get last five sample
        /// </summary>
        /// <returns>5 last sample</returns>
        public List<SampleADODTO> GetLastFiveSamples()
        {
            return baseRepository.CallStoredProcedure((cnn) =>
            {
                List<SampleADODTO> list = cnn.Query<SampleADODTO>(DatabaseQuery.GetStoredProcedured("GetLastFiveSamples"), commandType: CommandType.StoredProcedure).ToList();
                Trace.TraceInformation("Execure Stored Procedure: GetLastFiveSamples");
                return list.OrderBy(p => p.Id).ToList();
            });
        }

    }
}
